package pm.project.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import pm.db.DBUtil;
import pm.project.bean.ProjectBean;
import pm.user.bean.UserBean;

public class ProjectService {

	
	
	public boolean addOneProject(ProjectBean project) {
		boolean flag = false;
		Connection conn = DBUtil.getConnection();
		String sql = "insert into pm_project(projectname,projectcreator,workgroupname,projectdescribe,projectfilename,createtime,updatetime) values("
				+ "'"
				+ project.getProjectName()
				+ "'"
				+ ","
				+ "'"
				+ project.getProjectCreator()
				+ "'"
				+ ","
				+ "'"
				+ project.getWorkgroupName()
				+ "'"
				+ ","
				+ "'"
				+ project.getProjectDescribe() 
				+ "'"
				+ ","
				+ "'"
				+ project.getProjectFilename()
				+ "'"
				+ ","
				+ "'"
				+ project.getCreateTime()
				+ "'"
				+ ","
				+ "'"
				+ project.getUpdateTime() + "'" + ")";
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			past.executeUpdate();
			past.close();
			conn.close();
			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn);
		}

		return flag;
	}
	
	public boolean updateProject(ProjectBean project){

		boolean flag = false;
		Connection conn = DBUtil.getConnection();
		
		System.out.println("describe-->"+project.getProjectDescribe());
		String sql = " update pm_project set projectname=" + "'" + project.getProjectName() + "'"
				+ ", " + "workgroupname=" + "'" + project.getWorkgroupName() + "'"
				+ ", " + "projectdescribe=" + "'" + project.getProjectDescribe()+ "'"
				+ ", " + "updatetime=" + "'" + project.getUpdateTimestamp()+ "'"
				+ "where projectid =" + "'" + project.getProjectid() + "'";
		
		
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			past.executeUpdate();
			past.close();
			conn.close();
			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn);
		}

		return flag;
	
	}
	
	
	public List showAllProject(){
		List<ProjectBean> list = new ArrayList<ProjectBean>();

		String sql1 = "select * from pm_project";

		PreparedStatement past = null;
		Connection conn = DBUtil.getConnection();
		ResultSet rs = null;

		try {
			past = conn.prepareStatement(sql1);

			rs = past.executeQuery();
			while (rs.next()) {
				ProjectBean project = new ProjectBean();

				project.setProjectid(rs.getInt("projectid"));
				project.setProjectName(rs.getString("projectname"));
				project.setProjectDescribe(rs.getString("projectdescribe"));
				project.setWorkgroupName(rs.getString("workgroupname"));
				project.setProjectFilename(rs.getString("projectfilename"));
				project.setProjectCreator(rs.getString("projectcreator"));
				project.setCreateTime(rs.getTimestamp("createtime"));
				project.setUpdateTime(rs.getTimestamp("updatetime"));
				list.add(project);
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn);
		}
		return list;
	}
	
	public ProjectBean showProjectInfo(int projectid) {
		ProjectBean projectBean = new ProjectBean();
		String sql = "select * from pm_project where projectid = ?";
		Connection conn = DBUtil.getConnection();
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			past.setInt(1, projectid);
			ResultSet rs = past.executeQuery();
			if (!rs.next()) {
				return null;
			}
			projectBean.setProjectFilename(rs.getString("projectfilename"));
			projectBean.setProjectName(rs.getString("projectname"));
			projectBean.setWorkgroupName(rs.getString("workgroupname"));
			projectBean.setProjectDescribe(rs.getString("projectdescribe"));
			projectBean.setProjectCreator(rs.getString("projectcreator"));
			projectBean.setProjectid(rs.getInt("projectid"));
			projectBean.setCreateTime(rs.getTimestamp("createtime"));
			projectBean.setUpdateTime(rs.getTimestamp("updatetime"));
			

			rs.close();
			past.close();
			DBUtil.close(conn);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

		}
		return projectBean;
	}
	
	
	public String getFileName(int projectid){
		
		String fileName="";
		System.out.println("projectid-->"+projectid);
		String sql="select projectfilename from pm_project where projectid=?";
		Connection conn = DBUtil.getConnection();
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			past.setInt(1,projectid);
			ResultSet rs = past.executeQuery();
			if (!rs.next()) {
				return fileName;
			}

			fileName = rs.getString("projectfilename");
			System.out.println("salt in service-->" + fileName);
			rs.close();
			past.close();
			DBUtil.close(conn);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

		}

		return fileName;
	}
	
	public List projectAboutMe(String username){
		List<ProjectBean> list = new ArrayList<ProjectBean>();
String sql1="SELECT * FROM pm_project,pm_group WHERE pm_project.workgroupname=pm_group.workgroupname and pm_group.workgroupmember LIKE '%"+username+"%' ";


		PreparedStatement past = null;
		Connection conn = DBUtil.getConnection();
		ResultSet rs = null;

		try {
			past = conn.prepareStatement(sql1);

			rs = past.executeQuery();
			while (rs.next()) {
				ProjectBean project = new ProjectBean();

				project.setProjectid(rs.getInt("projectid"));
				project.setProjectName(rs.getString("projectname"));
				project.setProjectDescribe(rs.getString("projectdescribe"));
				project.setWorkgroupName(rs.getString("workgroupname"));
				project.setProjectFilename(rs.getString("projectfilename"));
				project.setProjectCreator(rs.getString("projectcreator"));
				project.setCreateTime(rs.getTimestamp("createtime"));
				System.out.println("time-->"+project.getCreateTime());
				project.setUpdateTime(rs.getTimestamp("updatetime"));
				list.add(project);
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(conn);
		}
		return list;
	}
	
	
	public boolean delectProject(int projectid){

		 boolean flag = false;
		 Connection conn = DBUtil.getConnection();
		 String sql = "delete from pm_project where projectid='"
		 + projectid + "'";
		 PreparedStatement past = null;
		 try {
		 past = conn.prepareStatement(sql);
		 past.executeUpdate();
		 past.close();
		 conn.close();
		 flag = true;
		 } catch (SQLException e) {
		 e.printStackTrace();
		 } finally {
			 DBUtil.close(conn);
		 }
		 return flag;
		 
	}

}
